﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
using System.Data.SqlClient;

namespace BLL
{
    public class VAITROBLL
    {
        DataService DB = new DataService();

        //get all values
        public List<VAITRO> getALL()
        {
            string sql = "select * from vaitro";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<VAITRO> lstVt = new List<VAITRO>();
            foreach (DataRow r in table.Rows)
            {
                VAITRO vt = new VAITRO();
                vt.mavaitro = (int)r[0];
                vt.tenvaitro = (string)r[1];
                lstVt.Add(vt);
            }
            return lstVt;
        }

        //insert value
        public void insert(string tenvaitro)
        {

            string sql = "insert into vaitro values (@tenvaitro)";
            SqlParameter paraTen = new SqlParameter("tenvaitro", tenvaitro);

            DB.ExecuteNonQuery(sql,paraTen);

        }

        public void update(int mavaitro, string tenvaitro)
        {
            string sql = "update vaitro set tenvaitro = N'" + tenvaitro + "' where mavaitro = " + mavaitro.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int mavaitro)
        {
            string sql = "delete from vaitro where mavaitro = " + mavaitro.ToString();
            DB.ExecuteNonQuery(sql);
        }

        //get count
        public int count()
        {
            string sql = "select count(*) from vaitro";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public VAITRO getTenvaitroWithTenTK(string tentaikhoan)
        {
 
            string sql = "select vt.mavaitro , vt.tenvaitro from vaitro vt , taikhoan tk where vt.mavaitro = tk.mavaitro and tk.tentaikhoan = '" + tentaikhoan +"'";

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            VAITRO vt = new VAITRO();
            vt.mavaitro = (int)r[0];
            vt.tenvaitro = (string)r[1];
    

            return vt;
        }
        public DataTable getallVaitro()
        {
            
        
            string sql = "select * from vaitro";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);
            return table;
        
        }
    }
}
